package com.css.bjdt_core.report.service;

import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

@Service
public class ReportTemplateCommentService {

	@Autowired
	private Dao dao;

	//报表批注验证，根据报表实例ID，和报表模板中的行列数，得到该单元格是否填写过批注
	public String getReportCellCommentCount(String templateId, Integer absColNum, Integer absRowNum, String annualId,
			String dzzId) {
		String tableName = "RPT_CELL_COMM_" + annualId;
		Sql strSql = Sqls.create("select count(*) from " + tableName + " where TEMPLATE_ID = @templateId"
				+ " and DZZ_ID = @dzzId and ABS_ROW_NUM = @absRowNum and ABS_COL_NUM = @absColNum");
		strSql.params().set("templateId", templateId).set("dzzId", dzzId).set("absRowNum", absRowNum).set("absColNum",
				absColNum);
		strSql.setCallback(Sqls.callback.integer());
		dao.execute(strSql);
		return strSql.getObject(Integer.class) + "";
	}

	public Map<String , Integer> getReportCellCommentCount(String templateId, String annualId, String dzzId) {
		String tableName = "RPT_CELL_COMM_" + annualId;
		Sql strSql = Sqls.create("select count(1) count, ABS_ROW_NUM, ABS_COL_NUM from $tableName where TEMPLATE_ID = @templateId " +
				" and DZZ_ID = @dzzId group by ABS_ROW_NUM ,ABS_COL_NUM");
		strSql.params().set("templateId", templateId).set("dzzId", dzzId);
		strSql.vars().set("tableName", tableName);
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				Map<String, Integer> res = new HashMap<>();
				while (rs.next()) {
					String key = rs.getString("ABS_ROW_NUM") + "_" + rs.getString("ABS_COL_NUM");
					res.put(key, rs.getInt("count"));
				}
				return res;
			}
		});
		dao.execute(strSql);
		return strSql.getObject(Map.class);
	}

}
